package com.hp.bon.sgw.service;

import com.hp.bon.sgw.domain.MsgTransRecord;
import org.hibernate.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;

import java.util.*;

public class OuterDBDataService
{
  private static final Logger logger = LoggerFactory.getLogger(OuterDBDataService.class);
  private SessionFactory outersessionFactory;

  public void saveToDB(Object data)
  {
    org.hibernate.Session session = null;
    Transaction tx = null;
    try {
      session = this.outersessionFactory.openSession();
      tx = session.beginTransaction();
      session.save(data);
      tx.commit();
    } catch (Exception e) {
      e.fillInStackTrace();
    } finally {
      if (session != null)
        session.close();
    }
  }

  public void execSQLToDB(int failedNum, org.hibernate.Session session, Transaction tx, Queue<String> dataSqlQueue)
  {
    String sqlStr = null;
    try {
      while ((sqlStr = (String)dataSqlQueue.poll()) != null)
        session.createSQLQuery(sqlStr).executeUpdate();
    }
    catch (Exception e) {
      failedNum++;
      logger.warn("FAILED SQL:" + sqlStr);
      if (!dataSqlQueue.isEmpty())
        execSQLToDB(failedNum, session, tx, dataSqlQueue);
    }
  }

  public boolean bulkSaveSqlToDayDB(List<String> destTableNames, String destTableNamePrefix, List<String> insertSql, String createTableSql)
  {
    String sqlGetExistTableNames = "select t.TNAME from TAB t where t.TNAME like '" + destTableNamePrefix.trim().toUpperCase() + "%' and t.TABTYPE='TABLE' order by t.TNAME";
    try
    {
      SQLQuery query = this.outersessionFactory.getCurrentSession().createSQLQuery(sqlGetExistTableNames);
      List queryList = query.list();
      if (queryList.isEmpty()) {
        logger.error("Cann't get data from sys.tab,sys.tab$,sys.obj$ ! Please authrize current DB user.");
        return false;
      }

      List<String> list = queryList;
      Set existTab = new HashSet();
      for (String table : list)
      {
        existTab.add(table);
      }
      for (String mustExist : destTableNames)
      {
        if (!existTab.contains(mustExist))
        {
          createTableSql = createTableSql.replaceAll("##TABLE_NAME##", mustExist);
          try
          {
            logger.info("Create table SQL:" + createTableSql);
            this.outersessionFactory.getCurrentSession().createSQLQuery(createTableSql).executeUpdate();
            logger.info("Table " + mustExist + " created.");
          }
          catch (Exception e)
          {
            logger.error("Table " + mustExist + " NOT created!");
            return false;
          }
        }

      }

      long start = System.currentTimeMillis();
      org.hibernate.Session session = null;
      Transaction tx = null;
      int failedNum = 0;
      try {
        session = this.outersessionFactory.openSession();
        tx = session.beginTransaction();
        Queue queue = new LinkedList();
        for (int i = 0; i < insertSql.size(); i++) {
          queue.offer((String)insertSql.get(i));
          if (i % 2000 == 0) {
            execSQLToDB(failedNum, session, tx, queue);
            session.flush();
            session.clear();
          }
        }
        execSQLToDB(failedNum, session, tx, queue);
        tx.commit();
        logger.info("total " + insertSql.size() + " records," + "save failed:" + failedNum + ", ms=" + (System.currentTimeMillis() - start));
      } catch (Exception e) {
        logger.warn("bulkSaveSqlToDayDB Exception when execSQLToDB.", e);
      } finally {
        if (session != null) {
          session.close();
        }
      }

    }
    catch (Exception e)
    {
      logger.warn("bulkSaveSqlToDayDB Unknow Exception.", e);
    }
    return true;
  }

  public boolean bulkSaveToDB(List<? extends Object> data) {
    org.hibernate.Session session = null;
    Transaction tx = null;
    long begin = System.currentTimeMillis();
    try {
      session = this.outersessionFactory.openSession();
    } catch (Exception e1) {
      logger.warn("can't connect db:" + e1);
      return false;
    }
    try {
      tx = session.beginTransaction();

      for (int i = 0; i < data.size(); i++) {
        session.save(data.get(i));
        if (i % 2000 == 0) {
          session.flush();
          session.clear();
        }
      }
      session.flush();
      session.clear();
      tx.commit();
    } catch (HibernateException e) {
        logger.error("HibernateException error",e);
      if (tx != null)
        tx.rollback();
    }
    finally
    {
      if (session != null) {
        session.close();
      }
    }
    long used = System.currentTimeMillis() - begin;

    if (used > 5000L) {
      logger.warn("insert to DB too slow ,record num=" + data.size() + ",used time ms=" + used);
    }

    return true;
  }

  public List<MsgTransRecord> getUnvalidateAnswers()
  {
    List list = new ArrayList();
    org.hibernate.Session session = null;
    try {
      session = this.outersessionFactory.openSession();
      String hql = "select t from MsgTransRecord t where t.msgType = :msgType and t.msgDetail is not null and t.validateCode is null ";
      Query query = session.createQuery(hql).setParameter("msgType", "WSA");
      list = query.list();
    } catch (Exception e) {
      logger.warn("can't connect db:" + e);
    }
    finally {
      if (session != null) {
        session.close();
      }
    }
    return list;
  }

  public void updateMsgTransRecords(List<MsgTransRecord> list) {
    org.hibernate.Session session = null;
    Transaction tx = null;
    try {
      session = this.outersessionFactory.openSession();
    } catch (Exception e1) {
      logger.warn("can't connect db:" + e1);
      return;
    }
    try {
      tx = session.beginTransaction();
      for (MsgTransRecord r : list) {
        session.update(r);
      }
      tx.commit();
    } catch (HibernateException e) {
      if (tx != null)
        tx.rollback();
    }
    finally {
      if (session != null)
        session.close();
    }
  }

  @Autowired
  @Qualifier("outerSessionFactory")
  public void setOutersessionFactory(SessionFactory outersessionFac) {
    this.outersessionFactory = outersessionFac;
  }
}